﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Agenda.Entities;
using System.Data;
using System.Data.OleDb;

namespace Agenda.Repositories
{
    public class SpoutRepository
    {
        private string connection = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" + DataConnection.DBConn + ";";

        public void Add(Spout spout)
        {
            using (OleDbConnection conn = new OleDbConnection(connection))
            {
                string statement =
                    string.Format("INSERT INTO Spout ({0}, {1}, {2}, {3}, {4}) VALUES(?, ?, ?, ?, ?)"
                    //, DBObjects.Spout.spoutId.ToString()
                    , DBObjects.Spout.obraId.ToString()
                    , DBObjects.Spout.diameter.ToString()
                    , DBObjects.Spout.pressure.ToString()
                    , DBObjects.Spout.rigidity.ToString()
                    , DBObjects.Spout.length.ToString());

                OleDbCommand command = new OleDbCommand(statement, conn);

                //command.Parameters.Add(DBObjects.Spout.spoutId.ToString(), OleDbType.VarChar).Value = spout.spoutId;
                command.Parameters.Add(DBObjects.Spout.obraId.ToString(), OleDbType.VarChar).Value = spout.obraId;
                command.Parameters.Add(DBObjects.Spout.diameter.ToString(), OleDbType.VarChar).Value = spout.diameter;
                command.Parameters.Add(DBObjects.Spout.pressure.ToString(), OleDbType.VarChar).Value = spout.pressure;
                command.Parameters.Add(DBObjects.Spout.rigidity.ToString(), OleDbType.VarChar).Value = spout.rigidity;
                command.Parameters.Add(DBObjects.Spout.length.ToString(), OleDbType.VarChar).Value = spout.length;

                DataConnection.Open(conn);

                command.ExecuteNonQuery();
            }
        }

        public Spout Get(int Id)
        {
            DataTable dt = new DataTable();

            using (OleDbConnection conn = new OleDbConnection(connection))
            {
                DataSet ds = new DataSet();

                OleDbDataAdapter adapter =
                    new OleDbDataAdapter("SELECT"
                        + "*"
                        + "FROM Spout", conn);

                DataConnection.Open(conn);

                adapter.Fill(ds);

                dt = ds.Tables[0];
            }
            return MapSpout(dt.Rows[0]);
        }

        public List<Spout> ListBy(DBObjects.Spout field, string q)
        {
            List<Spout> res = new List<Spout>();

            using (OleDbConnection conn = new OleDbConnection(connection))
            {
                DataSet ds = new DataSet("Spout");

                string statement =
                    string.Format("SELECT * FROM Spout WHERE {0} = {1} ORDER BY {0}"
                    , field.ToString()
                    , q);

                OleDbDataAdapter adapter = new OleDbDataAdapter(statement, conn);

                DataConnection.Open(conn);

                adapter.Fill(ds);
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    res.Add(MapSpout(dr));
                }
            }
            return res;
        }

        public void Update(Spout spout)
        {
            using (OleDbConnection conn = new OleDbConnection(connection))
            {
                string statement =
                    string.Format("UPDATE Spout ({0} = ?, {1} = ?, {2} = ?, {3} = ?, {4} = ?, {5} = ?, WHERE {6} = ?;)"
                    , DBObjects.Spout.obraId.ToString()
                    , DBObjects.Spout.diameter.ToString()
                    , DBObjects.Spout.pressure.ToString()
                    , DBObjects.Spout.rigidity.ToString()
                    , DBObjects.Spout.length.ToString()
                    , DBObjects.Spout.spoutId.ToString());

                OleDbCommand command = new OleDbCommand(statement, conn);

                command.Parameters.Add(DBObjects.Spout.obraId.ToString(), OleDbType.VarChar).Value = spout.obraId;
                command.Parameters.Add(DBObjects.Spout.diameter.ToString(), OleDbType.VarChar).Value = spout.diameter;
                command.Parameters.Add(DBObjects.Spout.pressure.ToString(), OleDbType.VarChar).Value = spout.pressure;
                command.Parameters.Add(DBObjects.Spout.rigidity.ToString(), OleDbType.VarChar).Value = spout.rigidity;
                command.Parameters.Add(DBObjects.Spout.length.ToString(), OleDbType.VarChar).Value = spout.length;
                command.Parameters.Add(DBObjects.Spout.spoutId.ToString(), OleDbType.VarChar).Value = spout.spoutId;

                DataConnection.Open(conn);

                command.ExecuteNonQuery();
            }
        }

        private Spout MapSpout(DataRow dr)
        {
            Spout spout = new Spout();

            spout.spoutId = Convert.ToInt32(dr[DBObjects.Spout.spoutId.ToString()].ToString());
            spout.obraId = Convert.ToInt32(dr[DBObjects.Spout.obraId.ToString()].ToString());
            spout.diameter = Convert.ToInt32(dr[DBObjects.Spout.diameter.ToString()].ToString());
            spout.pressure = Convert.ToInt32(dr[DBObjects.Spout.pressure.ToString()].ToString());
            spout.rigidity = Convert.ToInt32(dr[DBObjects.Spout.rigidity.ToString()].ToString());
            spout.length = Convert.ToInt32(dr[DBObjects.Spout.length.ToString()].ToString());

            return spout;
        }
    }
}
